package com.mjh.zhujianju.utils;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.livew.framework.exception.XTBusinessException;
import com.mjh.zhujianju.vo.base.SysUserVO;

public class ExcelUtil {

	private final static String excel2003 = ".xls";
	private final static String excel2007 = ".xlsx";

	/**
	 * @param in
	 * @param fileName
	 *            处理上传的excel文件
	 * @throws Exception 
	 *
	 */
	public static List<SysUserVO> getUserListByExcel(InputStream in, String fileName) throws Exception {
		List<SysUserVO> list = null;
		// 创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		list = new ArrayList<SysUserVO>();
		sheet = work.getSheetAt(0);
		if (sheet == null) {
			throw new Exception("用户信息为空了！");
		}
		for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
			row = sheet.getRow(j);
			if (row == null || row.getFirstCellNum() == j) {
				continue;
			}
			List<Object> li = new ArrayList<Object>();
			for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
				cell = row.getCell(y);
				li.add(cell);
			}
//			list.add(li);
		}
		work.close();
		return list;
	}

	// 判断excel文件的格式
	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003.equals(fileType)) {
			wb = new HSSFWorkbook(inStr);
		} else if (excel2007.equals(fileType)) {
			wb = new XSSFWorkbook(inStr);
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}
}
